<?php
/**
 * Jobs Model for Jobs World Component
 * 
 * @package    Joomla.Tutorials
 * @subpackage Components
 * @link http://docs.joomla.org/Developing_a_Model-View-Controller_Component_-_Part_2
 * @license    GNU/GPL
 */
 
// No direct access
 
defined( '_JEXEC' ) or die( 'Restricted access' );
 
jimport( 'joomla.application.component.model' );
 
/**
 * Jobs Model
 *
 * @package    Joomla.Tutorials
 * @subpackage Components
 */
class JobsModelJobs extends JModel
{
	/**
	 * Category ata array
	 *
	 * @var array
	 */
	var $_data = null;

	/**
	 * Category total
	 *
	 * @var integer
	 */
	var $_total = null;

	/**
	 * Pagination object
	 *
	 * @var object
	 */
	var $_pagination = null;

	/**
	 * Constructor
	 *
	 * @since 1.5
	 */
	function __construct()
	{
		parent::__construct();

		global $mainframe, $option;
	}
	
	function getOrder($id,$userid=0)
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT * FROM #__jobs where id = '.$id;
		if($userid>0) {
			$query .= " AND userid=".$userid;
		}
		//$query .= ' order by id asc';
	    $db->setQuery( $query );
	    $row = $db->loadObject();
        return $row;
    }
	function getAllOrdersUser($userid)
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT * FROM #__jobs WHERE userid = '.$userid;
	    $db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
    }
	
	function getNewOrdersUser($userid,$start=0,$limit=0)
    {
		$db =& JFactory::getDBO();
		$ql = '';
		if($limit>0) {
			$ql = ' limit '. $start . ',' . $limit;
		}
	    $query = 'SELECT * FROM #__jobs where userid = '.$userid . ' AND state =1 AND deadline > now() ORDER BY id DESC '. $ql;
		
	    $db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
    }
	
	function getOldOrdersUser($userid,$start=0,$limit=0)
    {
		$db =& JFactory::getDBO();
		$ql = '';
		if($limit>0) {
			$ql = ' limit '. $start . ',' . $limit;
		}
	    $query = 'SELECT * FROM #__jobs where userid = '.$userid . ' AND state=1 AND deadline < now() ORDER BY id DESC '. $ql;
	    $db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
    }

	function getOrders($start=0,$limit=0)
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT * FROM #__jobs WHERE state=1 ORDER BY id DESC';
		if($limit>0) {
			$query = 'SELECT * FROM #__jobs WHERE state=1 limit ' . $start . ',' . $limit . '  ORDER BY id DESC';
		}
	    $db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
    }
	
	function getOrdersAvailable($start=0,$limit=0)
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT * FROM #__jobs WHERE state=1 AND deadline > now() ORDER BY id DESC';
		if($limit>0) {
			$query = 'SELECT * FROM #__jobs WHERE state=1 AND deadline > now() LIMIT ' . $start . ',' . $limit . '  ORDER BY id DESC';
		}
	    $db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
    }
	
	function getCatering($id)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT a.*,b.name  FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE a.id='.$id;
	    $db->setQuery( $query );
	    $row = $db->loadObject();
        return $row;
	} 
	function getAllCatering()
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT a.*,b.name  FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE state=1 and rating=1 ORDER BY a.id DESC';
	    $db->setQuery( $query );
		$rows = $db->loadObjectList();
        return $rows;
	} 
	
	function saveJob($data)
	{
		$db =& JFactory::getDBO();
		$alias=JFilterOutput::stringURLSafe($data['title']);
		$query = "INSERT INTO ".$db->quoteName('#__jobs')." (".$db->quoteName('title')." ,".$db->quoteName('postcode')."
															,".$db->quoteName('city')." ,".$db->quoteName('deadline')."
															,".$db->quoteName('description')." ,".$db->quoteName('guests')."
															,".$db->quoteName('catid')." ,".$db->quoteName('language')."
															,".$db->quoteName('alias')." ,".$db->quoteName('userid')."
															,".$db->quoteName('address')."
															,".$db->quoteName('sub_food')."
															,".$db->quoteName('sub_food_other')."
															,".$db->quoteName('sub_drink')."
															,".$db->quoteName('sub_drink_other')."
															,".$db->quoteName('sub_room_time')."
															,".$db->quoteName('sub_room_country')."
															,".$db->quoteName('sub_rent')."
															,".$db->quoteName('sub_rent_desc')."
															,".$db->quoteName('offer')."
															,".$db->quoteName('start_hour')."
															,".$db->quoteName('start_minute').") 
													  VALUES(".$db->Quote($data['title'])." ,".$db->Quote($data['postcode'])."
															,".$db->Quote($data['city'])." ,".$db->Quote($data['date'])."
															,".$db->Quote($data['description'])." ,".$db->Quote($data['guests'])."
															,".$db->Quote(8)." ,".$db->Quote('*')." 
															,".$db->Quote($alias)." ,".$db->Quote($data['userid'])."
															,".$db->Quote($data['address']) ."
															,".$db->Quote($data['sub_food']) ."
															,".$db->Quote($data['sub_food_other']) ."
															,".$db->Quote($data['sub_drink']) ."
															,".$db->Quote($data['sub_drink_other']) ."
															,".$db->Quote($data['sub_room_time']) ."
															,".$db->Quote($data['sub_room_country']) ."
															,".$db->Quote($data['sub_rent']) ."
															,".$db->Quote($data['sub_rent_desc']) ."
															,".$db->Quote($data['offer']) ."
															,".$db->Quote($data['start_hour']) ."
															,".$db->Quote($data['start_minute']) .")";
		//echo $query;die();
		$db->setQuery( $query );
		if(!$db->query()) return false;
		return true;
	}
	function checkPoint($catererID)
	{
		$db =& JFactory::getDBO();
		$query = "SELECT point FROM  ".$db->quoteName('#__users')." WHERE ".$db->quoteName('id')." = ".$db->Quote($catererID);
		$db->setQuery( $query );
		//echo $query;
		$row = $db->loadObject();
		return $row;
	}
	function calculatePointCaterer($catererID,$updatePoint,$eventID)
	{
		$db =& JFactory::getDBO();
		// Check whether caterer applied the event or not
		$query = "SELECT count(*) num_rows FROM  ".$db->quoteName('#__jobs_user')." WHERE ".$db->quoteName('jobid')." = " .$db->Quote($eventID). " AND ".$db->quoteName('catererid')." = ".$db->Quote($catererID);
		$db->setQuery( $query );
		$row = $db->loadObject();
		//If not applied, update point and insert data to #__jobs_user table
		if(!$row->num_rows) 
		{
			// Update caterer's point
			$query = "UPDATE  ".$db->quoteName('#__users')." SET ".$db->quoteName('point')." = ".$db->Quote($updatePoint);
			$query .= " WHERE ".$db->quoteName('id')." = ".$db->Quote($catererID);
			$db->setQuery( $query );
			$return = $db->query();
			if(!$return) return false;
			
			//Dumping data into #__jobs_user table
			$query = "INSERT INTO ".$db->quoteName('#__jobs_user')."  (".$db->quoteName('jobid').",".$db->quoteName('catererid')." ,".$db->quoteName('state')."
																		,".$db->quoteName('paid').",".$db->quoteName('title').") 
																VALUES(".$db->Quote($eventID)." ,".$db->Quote($catererID)." ,".$db->Quote(1)."
																		,".$db->Quote(1).",".$db->Quote($eventID).")";
			$db->setQuery( $query );
			$return = $db->query();
			if(!$return) return false;
		}
		// If everything ok, fetch #__jobs table to get information
		$query = "SELECT a.address, DATE_FORMAT(a.deadline,'%d-%m-%Y') as deadline, a.guests, b.phone, b.city, b.postcode,a.title,a.sub_food,a. 	sub_food_other,a.sub_drink,a.sub_drink_other,a.sub_room_time,a.sub_room_country,a.sub_rent,a.sub_rent_desc,a.offer,a.start_hour,a.start_minute,b.email FROM ".$db->quoteName('#__jobs')." a LEFT JOIN ".$db->quoteName('#__users')." b ON a.userid = b.id";
		$query .=" WHERE a.id = ".$db->Quote($eventID);
		$db->setQuery( $query );
		$row = $db->loadObject();
		
		$offer =$row->offer;
		
		$arr_offer=$this->getCategory($row->offer);
		$row->offer=$arr_offer->title;
		
		$sub_food = @json_decode($row->sub_food);
		$sub_food_txt = '';
		for($i=0;$i<count($sub_food);$i++)
		{
			$sub_food_info = $this->getCategory($sub_food[$i]);
			if($i==count($sub_food)-1) { $sub_food_txt .= $sub_food_info->title ;} else {
			$sub_food_txt .= $sub_food_info->title . ' , ' ;
			}
		}
		$sub_drink = @json_decode($row->sub_drink);
		$sub_drink_txt = '';
		for($i=0;$i<count($sub_drink);$i++)
		{
			$sub_drink_info = $this->getCategory($sub_drink[$i]);
			if($i==count($sub_drink)-1) { $sub_drink_txt .= $sub_drink_info->title ;} else {
			$sub_drink_txt .= $sub_drink_info->title . ' , ' ;
			}
		}
		$sub_rent = $this->getCategory($row->sub_rent);
		$sub_rent_txt = $sub_rent->title;
		
		$sub_room_country = $this->getCategory($row->sub_room_country);
		$sub_room_country_txt = $sub_room_country->title;
		
		$data='';
		if($offer==14) {
			$data = '<span>Indhent tilbud på Catering</span>' . '<br>' ;
			$data .='<span>Ønsket mad: </span>'.$sub_food_txt. '<br>' ;
			$data .='<span>Andet mad: </span>'.$row->sub_food_other. '<br>' ;
			$data .='<span>Ønsket drikkevare: </span>'.$sub_drink_txt. '<br>' ;
			$data .='<span>Andet drikke: </span>'.$row->sub_drink_other. '<br>' ;
		}
		if($offer==15) {
			$data = '<span>Indhent tilbud på Leje af Kok</span>' . '<br>' ;
			$data .='<span>Antal timer: </span>'.$row->sub_room_time. '<br>' ;
			$data .='<span>Specialitet: </span>'.$sub_room_country_txt. '<br>' ;
		}
		if($offer==16) {
			$data = '<span>Indhent tilbud på Lokaler </span>' . '<br>' ;
			$data .='<span>Detaljer: </span>'.$sub_rent_txt. '<br>' ;
			$data .='<span>Beskrivelse af event: </span>'.$row->sub_rent_desc. '<br>' ;
		}
		$row->data=$data.'<br/>';
		
		return $row;
	}
	
	function getMyCatering($catererid)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT a.*,b.name FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE a.catererid='.$catererid . ' and rating = 1 and state=1';
		$db->setQuery( $query );
	    $row = $db->loadObject();
        return $row;
	}
	
	
	function getOthersCatering($catererid,$start=0,$limit=0)
	{
		$db =& JFactory::getDBO();
		$ql = '';
		if($limit>0) {
			$ql = ' limit '. $start . ',' . $limit;
		}
	   	$query = 'SELECT a.*,b.name  FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE state=1 AND a.catererid = '.$catererid.' ORDER BY a.id DESC ' . $ql;
		$db->setQuery( $query );
	    $rows = $db->loadObjectList();
		
        return $rows;
	} 
	function getUserInfo($userid)
    {
		$db =& JFactory::getDBO();
	    $query = 'SELECT * FROM #__users where id = '.$userid;
		$db->setQuery( $query );
	    $row = $db->loadObject();
        return $row;
    }
	function removeCatererOrders($catereid,$removeorder) {
		$db =& JFactory::getDBO();
		if(count($removeorder) >0) {
			foreach($removeorder as $item) {
					$query = 'UPDATE #__jobs_user SET state=-2 WHERE catererid=' . $catereid . ' AND id='.$item;
					$db->setQuery( $query );
					$db->loadObject();
			}
			
		} else {
			return false;
		}
		
	}
	
	function removeUserOrdersNew($userid,$removeorder) {
		$db =& JFactory::getDBO();
		if(count($removeorder) >0) {
			foreach($removeorder as $item) {
					$query = 'UPDATE #__jobs SET state=-2 WHERE userid=' . $userid . ' AND id='.$item;
					$db->setQuery( $query );
					$db->loadObject();
			}
			
		} else {
			return false;
		}
		
	}
	
	function removeUserOrdersOld($userid,$removeorder) {
		$db =& JFactory::getDBO();
		if(count($removeorder) >0) {
			foreach($removeorder as $item) {
					$query = 'UPDATE #__jobs SET state=-2 WHERE userid=' . $userid . ' AND id='.$item;
					$db->setQuery( $query );
					$db->loadObject();
			}
			
		} else {
			return false;
		}
		
	}
	
	function getAllUsersInOrder($orderid)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT a.*,b.name,b.description,b.image,b.phone,b.address,b.postcode,b.city,b.birthday FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE a.jobid='.$orderid . ' and a.state = 1 ';
		$db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
	}
	function updateRating($eventid,$catererid,$rservice,$rquality,$desc) {
		$db =& JFactory::getDBO();
		$query = 'UPDATE #__jobs_user SET rating=1,rating_service='.$rservice.',rating_quality='.$rquality.',description='.$db->quote($desc).' WHERE  catererid=' . $catererid . ' AND jobid='.$eventid;
		$db->setQuery( $query );
		//echo $query;
		//exit();
		$db->loadObject();
	}
	function getUsersRatingOfEvent($eventid)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT a.*,b.name,b.description,b.image,b.phone,b.address,b.postcode,b.city,b.birthday FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE a.jobid='.$eventid . ' AND rating=1 and state=1';
		$db->setQuery( $query );
	    $row = $db->loadObject();
        return $row;
	}
	function updateJob($data)
	{
		$db =& JFactory::getDBO();
		$alias=JFilterOutput::stringURLSafe($data['title']);
		$query = "UPDATE #__jobs SET catid=8,title=".$db->Quote($data['title']).",description=".$db->Quote($data['description']).",postcode=".$db->Quote($data['postcode']).",deadline=".$db->Quote($data['date'])
		.",city=".$db->Quote($data['city'])
		
		.",sub_food=".$db->Quote($data['sub_food'])
		.",sub_food_other=".$db->Quote($data['sub_food_other'])
		.",sub_drink=".$db->Quote($data['sub_drink'])
		.",sub_drink_other=".$db->Quote($data['sub_drink_other'])
		.",sub_room_time=".$db->Quote($data['sub_room_time'])
		.",sub_room_country=".$db->Quote($data['sub_room_country'])
		.",sub_rent=".$db->Quote($data['sub_rent'])
		.",sub_rent_desc=".$db->Quote($data['sub_rent_desc'])
		.",start_hour=".$db->Quote($data['start_hour'])
		.",start_minute=".$db->Quote($data['start_minute'])
		.",offer=".$db->Quote($data['offer'])
		
		.",address=".$db->Quote($data['address']).",guests=".$db->Quote($data['guests'])." WHERE  id=" . $data['id'];
		
		
		/*
		$query = "INSERT INTO ".$db->quoteName('#__jobs')." (".$db->quoteName('title')." ,".$db->quoteName('postcode')."
															,".$db->quoteName('city')." ,".$db->quoteName('deadline')."
															,".$db->quoteName('description')." ,".$db->quoteName('guests')."
															,".$db->quoteName('catid')." ,".$db->quoteName('language')."
															,".$db->quoteName('alias')." ,".$db->quoteName('userid')."
															,".$db->quoteName('address').") 
													  VALUES(".$db->Quote($data['title'])." ,".$db->Quote($data['postcode'])."
															,".$db->Quote($data['city'])." ,".$db->Quote($data['date'])."
															,".$db->Quote($data['description'])." ,".$db->Quote($data['guests'])."
															,".$db->Quote(8)." ,".$db->Quote('*')." 
															,".$db->Quote($alias)." ,".$db->Quote($data['userid'])."
															,".$db->Quote($data['address']).")";
		*/													
		//echo $query;die();
		$db->setQuery( $query );
		if(!$db->query()) return false;
		return true;
	}
	function search($data)
	{
		$db =& JFactory::getDBO();
		$query = "SELECT * FROM ".$db->quoteName('#__jobs')." a WHERE a.id <> '' AND a.state = 1 ";
		if(!empty($data['eventname'])){
			$query .=" AND a.title LIKE ".$db->Quote("%".$data['eventname']."%")." ";
		}
		if(!empty($data['postcode'])){
			$query .=" AND a.postcode LIKE ".$db->Quote("%".$data['postcode']."%");
		}
		if(!empty($data['city'])){
			$query .=" AND a.city LIKE ".$db->Quote("%".$data['city']."%");
		}
		if(!empty($data['quantity'])){
			$query .=" AND a.guests = ".$db->Quote($data['quantity'])." ";
		}
		if(!empty($data['deadline'])){
			$query .=" AND DATE(a.deadline) <= ".$db->Quote($data['deadline'])." ";
		}
		$query .=" ORDER BY a.deadline DESC, a.title ASC ";
		//echo $query;die;
		$db->setQuery( $query );
		$rows = $db->loadObjectList();
        return $rows;
	}
	
	function addPaymentHistory($userid,$orderid,$txnid,$amount) {
		
		$db =& JFactory::getDBO();
		$amount = $amount * 100;
		$query = "INSERT INTO ".$db->quoteName('#__payment_history')." (".$db->quoteName('created').",".$db->quoteName('title').",".$db->quoteName('userid').",".$db->quoteName('orderid').",".$db->quoteName('txnid').",".$db->quoteName('point').",".$db->quoteName('state').") values(".JFactory::getDate()->toMySQL()." ".$db->Quote($orderid)." ,".$db->Quote($userid)." ,".$db->Quote($orderid).','.$db->Quote($txnid)." ,".$db->Quote($amount).",0)";
		$db->setQuery( $query );
		$return = $db->query();
		$this->activePaymentHistory($orderid);
	}
	
	function activePaymentHistory($orderid) {
		
		
		$file = 'test.txt';

		$db =& JFactory::getDBO();
		
		$query = "SELECT point,userid FROM ".$db->quoteName('#__payment_history') . " WHERE orderid=".$orderid . ' and state = 0';
		$db->setQuery( $query );
		
		
		//file_put_contents($file, $query.'  ', FILE_APPEND | LOCK_EX);
		
		$row = $db->loadObject();
		if(isset($row)) {
			$amount = $row->point;
			$userid = $row->userid;
			
			$query = "SELECT point FROM ".$db->quoteName('#__users') . " WHERE id=".$userid;
			$db->setQuery( $query );
			//file_put_contents($file, $query . '  ', FILE_APPEND | LOCK_EX);
			
			$row = $db->loadObject();
			$point = $amount + $row->point;
			$query = "UPDATE  ".$db->quoteName('#__users')." SET ".$db->quoteName('point')." = ".$db->Quote($point);
			$query .= " WHERE ".$db->quoteName('id')." = ".$db->Quote($userid);
			//file_put_contents($file, $query .'  ', FILE_APPEND | LOCK_EX);
			$db->setQuery( $query );
			$return = $db->query();
			
			$query = "UPDATE  ".$db->quoteName('#__payment_history')." SET ".$db->quoteName('state')." = 1 WHERE ".$db->quoteName('orderid')." = ".$db->Quote($orderid);
			//file_put_contents($file, $query .'  ', FILE_APPEND | LOCK_EX);
			$db->setQuery( $query );
			$return = $db->query();
			
		}
		
	}
	function checkUserPaid($userID,$eventID)
	{
		$db =& JFactory::getDBO();
		// Check whether user paid or not
		$query = "SELECT paid FROM  ".$db->quoteName('#__jobs')." WHERE ".$db->quoteName('id')." = " .$db->Quote($eventID). " AND ".$db->quoteName('userid')." = ".$db->Quote($userID);
		$db->setQuery( $query );
		$row = $db->loadObject();
		return $row->paid;
	}
	function calculatePointUser($userID,$updatePoint,$eventID)
	{
		$db =& JFactory::getDBO();
		// Check whether user paid or not
		$paid = $this->checkUserPaid($userID,$eventID);
		//If not paid, update point and set paid = 1
		if(!$paid) 
		{
			// Update caterer's point
			$query = "UPDATE  ".$db->quoteName('#__users')." SET ".$db->quoteName('point')." = ".$db->Quote($updatePoint);
			$query .= " WHERE ".$db->quoteName('id')." = ".$db->Quote($userID);
			$db->setQuery( $query );
			$return = $db->query();
			if(!$return) return false;
			// Update paid = 1
			$query = "UPDATE  ".$db->quoteName('#__jobs')." SET ".$db->quoteName('paid')." = ".$db->Quote(1);
			$query .= " WHERE ".$db->quoteName('id')." = ".$db->Quote($eventID);
			$db->setQuery( $query );
			$return = $db->query();
			if(!$return) return false;
		}
		// If everything ok, fetch #__jobs table to get information
		$query = 'SELECT a.*,b.name,b.description,b.image,b.phone,b.address,b.postcode,b.city,b.birthday FROM #__jobs_user a LEFT JOIN #__users b ON a.catererid=b.id WHERE a.jobid='.$eventID . ' and a.state = 1 ';
		$db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
	}
	function getAllPaymentOrder($userid)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT * FROM #__payment_history WHERE userid='.$userid ;
		$db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
	}
	
	function getCategorys($componentname)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT * FROM #__categories WHERE extension="'.$componentname . '" and published=1';
		//echo $query;
		$db->setQuery( $query );
	    $rows = $db->loadObjectList();
        return $rows;
	}
	function getCategory($id)
	{
		$db =& JFactory::getDBO();
	   	$query = 'SELECT * FROM #__categories WHERE id='.$id;
		//echo $query;
		$db->setQuery( $query );
	    $rows = $db->loadObject();
        return $rows;
	}
}